# coding=utf-8

import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import Reference, LineChart
from openpyxl.utils.dataframe import dataframe_to_rows


def export_data(p_data,
                p_file_path='output.xlsx'):
    df = pd.DataFrame(p_data)
    wb = Workbook()
    ws = wb.active
    for r in dataframe_to_rows(df, index=False, header=True):
        ws.append(r)
    wb.save(p_file_path)


def export_with_chart(p_data,
                      p_title='Title',
                      p_x_title='x_Title',
                      p_y_title='y_Title',
                      p_file_path='output.xlsx',
                      ):
    df = pd.DataFrame(p_data)
    wb = Workbook()
    ws = wb.active
    for r in dataframe_to_rows(df, index=False, header=True):
        ws.append(r)
    l_chart = LineChart()

    l_chart.width = 30
    l_chart.height = 12

    l_chart.title = p_title
    l_chart.x_axis.title = p_x_title
    l_chart.y_axis.title = p_y_title

    c_x = Reference(ws, min_col=3, min_row=2, max_row=len(df))
    c_y = Reference(ws, min_col=4, min_row=1, max_col=6, max_row=len(df))

    l_chart.add_data(c_y, titles_from_data=True)
    l_chart.set_categories(c_x)

    ws.add_chart(l_chart, "A44")
    wb.save(p_file_path)


if __name__ == '__main__':
    # 示例数据
    data = {'Name': ['Alice', 'Bob', 'Charlie'],
            'Age': [25, 10, 35],
            'Salary': [50, 6, 70]}
    export_with_chart(data)
